package arquitetura.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import conexao.Conexao;
import conexao.ConexaoImpl;
import to.Fornecedor;
import exceptions.*;

public class ModelFornecedorImpl implements ModelFornecedor{
	private Conexao conexao;
	
	public ModelFornecedorImpl(){
		conexao = ConexaoImpl.getInstancia();
    }
	
	public void incluir(Fornecedor fornecedor) throws ConexaoException, ModelException{
        Connection conex = conexao.conectar();
        int ultimoCodigo = conexao.ultimoCodigo("Fornecedor");
        
        String sql = "INSERT INTO Fornecedor(codigo, razao_social, cnpj, endereco, fone_contato, nome_contato, email_contato) " + 
        			 "VALUES(?,?,?,?,?,?,?)";
        try {
            PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setInt(1, ultimoCodigo + 1);
            pstm.setString(2, fornecedor.getRazaoSocial());
            pstm.setString(3, fornecedor.getCnpj());
            pstm.setString(4, fornecedor.getEndereco());
            pstm.setString(5, fornecedor.getFoneContato());
            pstm.setString(6, fornecedor.getNomeContato());
            pstm.setString(7, fornecedor.getEmailContato());
            pstm.executeUpdate();
            pstm.close();
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);
        }
    }
	
	public void alterar(Fornecedor fornecedor) throws ConexaoException, ModelException{		
		Connection conex = conexao.conectar();
	        
	    String sql = "UPDATE Fornecedor SET razao_social=?, cnpj=?, endereco=?, fone_contato=?, nome_contato=?, email_contato=? " + 
	    			 "WHERE codigo=?";
	    try {
	        PreparedStatement pstm = conex.prepareStatement(sql);	            
	        pstm.setString(1, fornecedor.getRazaoSocial());
	        pstm.setString(2, fornecedor.getCnpj());	        
	        pstm.setString(3, fornecedor.getEndereco());
	        pstm.setString(4, fornecedor.getFoneContato());
	        pstm.setString(5, fornecedor.getNomeContato());
	        pstm.setString(6, fornecedor.getEmailContato());
	        pstm.setInt(7, fornecedor.getCodigo());
	        pstm.executeUpdate();
	        pstm.close();
	    } catch (SQLException e) {
	        throw new ModelException(e.getMessage());
	    }finally{
	    	conexao.desconectar(conex);
	    }
	}
	
	public void excluir(int codigo)throws ConexaoException, ModelException{		
		Connection conex = conexao.conectar();		
		String sql = "DELETE FROM Fornecedor WHERE codigo=?";
		try{
			PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setInt(1, codigo);
            pstm.executeUpdate();
            pstm.close();
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);
        }
	}
	
	public Fornecedor consultar(int codigo) throws ConexaoException, ModelException{
		Fornecedor fornecedor = null;
		
		Connection conex = conexao.conectar();		
		String sql = "SELECT * FROM Fornecedor WHERE codigo = ?";
		
		try{
			PreparedStatement pstm = conex.prepareStatement(sql);
			pstm.setInt(1, codigo);
            ResultSet rs = pstm.executeQuery();
		
			if (rs.next()) {
				fornecedor = new Fornecedor();
				
				fornecedor.setCodigo(rs.getInt("codigo"));
				fornecedor.setRazaoSocial(rs.getString("razao_social"));
				fornecedor.setCnpj(rs.getString("cnpj"));
				fornecedor.setEndereco(rs.getString("endereco"));
				fornecedor.setFoneContato(rs.getString("fone_contato"));
				fornecedor.setNomeContato(rs.getString("nome_contato"));
				fornecedor.setEmailContato(rs.getString("email_contato"));
			}
			return fornecedor;
	    }catch(SQLException e){
	        throw new ModelException(e.getMessage());
	    }finally{
	    	conexao.desconectar(conex);
	    }
	}
	
	public Collection<String []> listarParaGrid(String parteDoNome) throws ConexaoException, ModelException {
		Collection<String []> lista = new ArrayList<String []>();
		
		Connection conex = conexao.conectar();		
		String sql = "SELECT Codigo, Razao_Social, Fone_Contato " +
					 "FROM Fornecedor ";
		
		if (! parteDoNome.trim().equals(""))
			sql = sql + "WHERE Razao_Social like \"%" + parteDoNome + "%\" ";
		
		sql = sql + "ORDER BY Razao_Social";
		try{
			PreparedStatement stm = conex.prepareStatement(sql);
            ResultSet rs = stm.executeQuery();
            
            while (rs.next())
            	lista.add(new String[] {rs.getString("codigo"), rs.getString("razao_social"), rs.getString("fone_contato")});
			
			return lista;
	    }catch(SQLException e){
	        throw new ModelException(e.getMessage());
	    }finally{
	    	conexao.desconectar(conex);
	    }
	}
	
	public boolean cnpjJahCadastrado(Fornecedor fornecedor) throws ConexaoException, ModelException{
		Connection conex = conexao.conectar();
		boolean jahExisteRegistro = false;
		int codigo = fornecedor.getCodigo();
		String cnpj = fornecedor.getCnpj();
		String sql = "";
        
		if (codigo == 0)
			sql = "SELECT Codigo FROM Fornecedor WHERE Cnpj is not null AND Cnpj = ?";
		else
			sql = "SELECT Codigo FROM Fornecedor WHERE Cnpj is not null AND Cnpj = ? AND Codigo <> " + codigo;
		
        try {
            PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setString(1, cnpj);
            ResultSet rs = pstm.executeQuery();            
            jahExisteRegistro = rs.next();
            pstm.close();
            
            return jahExisteRegistro;
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);        	
        }
	}
	
	public boolean podeExcluirFornecedor(int codigo) throws ConexaoException, ModelException{
		return (! existeMidia(codigo));
	}
	
	private boolean existeMidia(int codigo) throws ConexaoException, ModelException{
		Connection conex = conexao.conectar();
		boolean jahExisteRegistro = false;
		
		String sql = "SELECT Codigo FROM Midia WHERE Fornecedor = " + codigo;		
        try {
            PreparedStatement pstm = conex.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();
            
            jahExisteRegistro = rs.next();
            pstm.close();
            
            return jahExisteRegistro;
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);        	
        } 
	}
}